package edu.cs.usask.ca.silver.persistence.sql;

/**
 * Collection of SQL Strings for operations related to model versions. These
 * strings act on the MVersion, Scenario, and ScenarioCollection tables.
 * 
 * @author Daniel Funk
 * @author Yudi Xue
 */
public interface SQLModelVersionStringsI {

	// The mv_ID_per_mp is the ID of this version within the current project.
	// This number is
	// different than the id generated. This is necessary since all versions (no
	// matter which
	// project) are stored in the same table. Each version has a unique ID
	// across the system,
	// called mv_id, and a unique id within the project, called mv_ID_per_mp.
	// The value for
	// this ID is retrieved from the ModelProject table upon insert into
	// MVersion.

	// Update: since the use of UUID, it is unnecessary to track the model
	// version within a model project.
	// We only need to use
	public static final String MODEL_VERSION_INSERT = "INSERT INTO MVersion (mv_identity, mv_version, mv_update_time, mv_name, mv_creator, mv_type, mv_creationDate, mv_description, mv_mp_identity, mv_original_s, mv_baseline_s, mv_sourceFile, mv_visibility)"
			+ "VALUES ('%s', '%s', CURRENT_TIMESTAMP, '%s', '%s', '%s', CURRENT_TIMESTAMP, '%s', '%s', null, null, null, %d)";

	public static final String MODEL_VERSION_SELECT = "SELECT mv_identity, mv_version, mv_update_time, mv_name, mv_creator, mv_type, mv_creationDate, mv_description, "
			+ "mv_mp_identity, mv_visibility "
			+ "FROM MVersion WHERE mv_identity = '%s'";
	// TODO SELECT MV with source or without source.
	// public static final String MODEL_VERSION_SELECT =
	// "SELECT mv_ID, mv_ID_per_mp, mv_name, mv_creator, mv_type, mv_creationDate, mv_description, "
	// +
	// "mv_mp_ID,mv_original_s, mv_baseline_s, mv_sourceFile, mv_visibility " +
	// "FROM MVersion WHERE mv_identity = '%s'";

	public static final String MODEL_VERSION_SELECT_ANY = "SELECT * FROM MVersion";

	public static final String MODEL_VERSION_SELECT_VERSION = "SELECT mv_version FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_LAST_UPDATE_TIME = "SELECT mv_update_time FROM MVersion WHERE mv_identity = '%s'";

	public static final String MODEL_VERSION_SELECT_NAME = "SELECT mv_name FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_CREATOR = "SELECT mv_creator FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_TYPE = "SELECT mv_type FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_CREATIONDATE = "SELECT mv_creationDate FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_DESCRIPTION = "SELECT mv_description FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_MPID = "SELECT mv_mp_identity FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_SOURCEFILE = "SELECT mv_sourceFile FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_SOURCEFILE_NAME = "SELECT f_name FROM File WHERE File.f_identity = (SELECT mv_sourceFile FROM MVersion) AND (SELECT mv_identity From MVersion) = '%s'";

	public static final String MODEL_VERSION_SELECT_SIDS = "SELECT s_identity FROM Scenario WHERE s_mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_SIDANDNAME = "SELECT s_identity , s_name FROM Scenario WHERE s_mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_ORIGINALS = "SELECT mv_original_s FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_BASELINES = "SELECT mv_baseline_s FROM MVersion WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_MOSTRECENTSID = "SELECT s_identity FROM Scenario WHERE s_mv_identity = '%s' ORDER BY s_creationDate DESC LIMIT 1";

	public static final String MODEL_VERSION_SELECT_SCIDS = "SELECT sc_identity FROM ScenarioCollection WHERE sc_mv_identity = '%s'";
	public static final String MODEL_VERSION_SELECT_SCIDANDNAME = "SELECT sc_identity, sc_name FROM ScenarioCollection WHERE sc_mv_identity = '%s'";
	// DERBY does not support `LIMIT` -check
	// http://db.apache.org/derby/faq.html#limit
	// public static final String MODEL_VERSION_SELECT_MOSTRECENTSCID =
	// "SELECT sc_identity FROM ScenarioCollection WHERE sc_mv_identity = '%s' ORDER BY sc_creationDate DESC LIMIT 1";
	// public static final String MODEL_VERSION_SELECT_MOSTRECENTSCID =
	// "SELECT sc_identity FROM ScenarioCollection WHERE sc_mv_identity = '%s' ORDER BY sc_creationDate DESC AND rownum = 1";

	public static final String MODEL_VERSION_SET_IDENTITY = "UPDATE MVersion SET mv_identity = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SET_VERSION = "UPDATE MVersion SET mv_version = '%s' , mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";

	public static final String MODEL_VERSION_SET_NAME = "UPDATE MVersion SET mv_name = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SET_CREATOR = "UPDATE MVersion SET mv_creator = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SET_TYPE = "UPDATE MVersion SET mv_type = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SET_DESCRIPTION = "UPDATE MVersion SET mv_description = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SET_BASELINES = "UPDATE MVersion SET mv_baseline_s = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";
	public static final String MODEL_VERSION_SET_ORIGINALS = "UPDATE MVersion SET mv_original_s = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";

	public static final String MODEL_VERSION_SET_SOURCEFILEID = "UPDATE MVersion SET mv_sourceFile = '%s', mv_update_time = CURRENT_TIMESTAMP WHERE mv_identity = '%s'";

	public static final String MODEL_VERSION_DELETE = "DELETE FROM MVersion WHERE mv_identity = '%s'";

	public static final String MODEL_VERSION_SELECT_ASSOCIATEDFILEIDS = "SELECT mv2f_f_identity FROM MV2F WHERE mv2f_mv_identity = '%s'";

}
